package dao.oracle;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Calendar;
import java.util.Date;

import transferobject.Bid;
import dao.BidDao;

/**
 * Bid DAO for Oracle database
 * @author Ilya_Poliakov
 */
public class OracleBidDao implements BidDao {

	private static final String ADD_BID_QUERY = "INSERT INTO bids VALUES (null, ?, ?, ?)";
	private static final String BID_FIELD_NAME = "bid";
	private static final String BID_ID_FIELD_NAME = "bid_id";
	private static final String BID_INCREMENT_FIELD_NAME = "bid_increment";
	private static final String BIDDER_ID_FIELD_NAME = "bidder_id";
	private static final String BUY_IT_NOW_FIELD_NAME = "buy_it_now";
	private static final String CHECK_BID_QUERY = "SELECT buy_it_now, start_price, bid, bid_increment, "
			+ "start_bidding_date, time_left FROM full_info WHERE item_id = ?";
	private static final String DELETE_BID_QUERY = "DELETE FROM bids WHERE bid_id = ?";
	private static final String FIND_BID_QUERY = "SELECT * FROM bids WHERE bid_id = ?";
	private static final String ITEM_ID_FIELD_NAME = "item_id";
	private static final String START_DATE_FIELD_NAME = "start_bidding_date";
	private static final String START_PRICE_FIELD_NAME = "start_price";
	private static final String TIME_LEFT_FIELD_NAME = "time_left";
	private static final String UPDATE_BID_QUERY = "UPDATE bids SET bidder_id = ?, "
			+ "bid = ?, item_id = ? WHERE bid_id = ?";
	private final OracleConnection connection;

	/**
	 * Create new OracleBidDao
	 * @param Connection OracleConnection object
	 */
	public OracleBidDao(OracleConnection connection) {
		super();
		this.connection = connection;
	}

	/**
	 * {@inheritDoc}
	 */
	public boolean deleteBid(int bidId) {
		boolean result = false;
		try {
			Connection cn = connection.getConnection();
			PreparedStatement ps = cn.prepareStatement(DELETE_BID_QUERY);
			ps.setInt(1, bidId);
			result = ps.executeUpdate() > 0;
			ps.close();
			cn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * {@inheritDoc}
	 */
	public Bid findBid(int bidId) {
		Bid bid = null;
		try {
			Connection cn = connection.getConnection();
			PreparedStatement ps = cn.prepareStatement(FIND_BID_QUERY);
			ps.setInt(1, bidId);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				int bidderId = rs.getInt(BIDDER_ID_FIELD_NAME);
				float bidValue = rs.getFloat(BID_FIELD_NAME);
				int itemId = rs.getInt(ITEM_ID_FIELD_NAME);
				bid = new Bid(bidId, bidderId, bidValue, itemId);
			}
			rs.close();
			ps.close();
			cn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return bid;
	}

	/**
	 * {@inheritDoc}
	 */
	public int insertBid(Bid bid) {
		int result = 0;

		try {
			Connection cn = connection.getConnection();
			if (isBidPossible(cn, bid)) {
				String[] fields = new String[] { BID_ID_FIELD_NAME };
				PreparedStatement ps = cn.prepareStatement(ADD_BID_QUERY,
						fields);
				ps.setInt(1, bid.getBidderId());
				ps.setFloat(2, bid.getBid());
				ps.setInt(3, bid.getItemId());
				result = ps.executeUpdate();
				ResultSet generatedKeys = ps.getGeneratedKeys();
				if (generatedKeys.next()) {
					result = (int) generatedKeys.getLong(1);
					bid.setBidId(result);
				}
				ps.close();
			}
			cn.commit();
			cn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	/*
	 * Check The bidding possibility
	 * @param Bid bid for adding
	 * @return Is bidding available or not
	 */
	private boolean isBidPossible(Connection cn, Bid bid) {
		boolean result = false;
		try {
			cn.setAutoCommit(false);
			PreparedStatement ps = cn.prepareStatement(CHECK_BID_QUERY);
			ps.setInt(1, bid.getItemId());
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				boolean buyItNow = (rs.getInt(BUY_IT_NOW_FIELD_NAME) == 1);
				float startPrice = rs.getFloat(START_PRICE_FIELD_NAME);
				float bidValue = rs.getFloat(BID_FIELD_NAME);
				float increment = rs.getFloat(BID_INCREMENT_FIELD_NAME);
				int timeLeft = rs.getInt(TIME_LEFT_FIELD_NAME);
				Calendar endDate = Calendar.getInstance();
				endDate.setTime(rs.getTimestamp(START_DATE_FIELD_NAME));
				endDate.add(Calendar.HOUR_OF_DAY, timeLeft);
				Date now = new Date();
				boolean notExpired = ((timeLeft == 0) || endDate
						.getTimeInMillis() > now.getTime());
				if (buyItNow) {
					result = notExpired && (bid.getBid() == startPrice);
				} else {
					float minBid = Math.max(startPrice, bidValue) + increment;
					result = notExpired && (bid.getBid() >= minBid);
				}
			}
			rs.close();
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * {@inheritDoc}
	 */
	public boolean updateBid(Bid bid) {
		boolean result = false;
		try {
			Connection cn = connection.getConnection();
			if (isBidPossible(cn, bid)) {
				PreparedStatement ps = cn.prepareStatement(UPDATE_BID_QUERY);
				ps.setInt(1, bid.getBidderId());
				ps.setFloat(2, bid.getBid());
				ps.setInt(3, bid.getItemId());
				ps.setInt(4, bid.getBidId());
				result = ps.executeUpdate() > 0;
				ps.close();
			}
			cn.commit();
			cn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

}
